PostgreSQL PGPOOL-II YUM 部署

1 背景知识

本文主要介绍如何使用 yum 进行 PGPOOL-II 安装和部署。

2 Yum Repository PGPOOL-II

2.1 安装 PGPOOL-II Repository

dnf install https://www.pgpool.net/yum/rpms/4.4/redhat/rhel-8-x86_64/pgpool-II-release-4.4-1.noarch.rpm -y

2.2 查看 PGPOOL-II 所有支持的版本

yum list pgpool-II* 

2.3 安装指定pg15版本

yum install -y pgpool-II-pg15-*

3 创建数据库用户

 psql -U postgres -p 5432
CREATE ROLE pgpool WITH PASSWORD 'pgpool' LOGIN;

4 配置SSH 免密

Linux SSH 配置节点互信

5 配置数据库密码文件

su - postgres
vi ~/.pgpass
node1:5432:replication:repl:repl
node2:5432:replication:repl:repl
node3:5432:replication:repl:repl
node1:5432:*:postgres:postgres
node2:5432:*:postgres:postgres
node3:5432:*:postgres:postgres

chmod 600  ~/.pgpass
scp ~/.pgpass node1:~
scp ~/.pgpass node2:~
scp ~/.pgpass node3:~

6 创建 pgpool_node_id 文件

su - root

cat > /etc/pgpool-II/pgpool_node_id << EOF
0
EOF

• node2

cat > /etc/pgpool-II/pgpool_node_id<< EOF
1
EOF

• node3

cat > /etc/pgpool-II/pgpool_node_id << EOF
2
EOF

7 配置 pgpool.conf

# 流复制模式
backend_clustering_mode ='streaming_replication'
# 通用设置
listen_addresses = '*' 
 # 端口
port = 9999

# 流复制检查
sr_check_user = 'pgpool'
sr_check_password = ''
# 健康检查 
health_check_period = 5
health_check_timeout = 30
health_check_user = 'pgpool'
health_check_password = ''
health_check_max_retries = 3
# 设置postgres 服务器
# - Backend Connection Settings -

backend_hostname0 = 'node1'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/usr/local/pgsql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = 'node2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/usr/local/pgsql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

backend_hostname2 = 'node3'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/usr/local/pgsql/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'

# 失败切换配置
failover_command = '/usr/local/pgpool/etc/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
follow_primary_command = '/usr/local/pgpool/etc/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'
# 在线恢复功能配置 
recovery_user = 'postgres'
recovery_password = 'postgres'
recovery_1st_stage_command = 'recovery_1st_stage'
# 客户端认证 
enable_pool_hba = on
# 看门狗配置 
use_watchdog = on
delegate_ip = '192.168.10.191'
if_cmd_path = '/sbin'
arping_path = '/usr/sbin'
if_up_cmd = '/sbin/ip addr add 192.168.10.191/24 dev ens192 label ens192:0' 
if_down_cmd = '/sbin/ip addr del 192.168.10.191/24 dev ens192:0' 
arping_cmd = '/usr/sbin/arping -U 192.168.10.192 -w 1 -I ens192'

hostname0 = 'node1'
wd_port0 = 9000
pgpool_port0 = 9999

hostname1 = 'node2'
wd_port1 = 9000
pgpool_port1 = 9999

hostname2 = 'node3'
wd_port2 = 9000
pgpool_port2 = 9999

wd_lifecheck_method = 'heartbeat'
wd_interval = 10

heartbeat_hostname0 = 'node1'
heartbeat_port0 = 9694
heartbeat_device0 = ''
heartbeat_hostname1 = 'node2'
heartbeat_port1 = 9694
heartbeat_device1 = ''
heartbeat_hostname2 = 'node3'
heartbeat_port2 = 9694
heartbeat_device2 = ''
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30

wd_escalation_command = '/usr/local/pgpool/etc/escalation.sh'
# 日志记录 配置
log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/pgpool_log'
log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB

8 配置pcp.conf

echo 'pgpool:'`pg_md5 pgpool` > /etc/pgpool-II/pcp.conf
pgpool:3d27c2e24377377bdd907962a53e13eb

echo 'localhost:9898:pgpool:pgpool' > ~/.pcppass
chmod 600 ~/.pcppass

9 配置脚本

9.1 失败切换脚本

1、模版拷贝

cp -p /etc/pgpool-II/sample_scripts/failover.sh.sample /etc/pgpool-II/failover.sh
 cp -p /etc/pgpool-II/sample_scripts/follow_primary.sh.sample /etc/pgpool-II/follow_primary.sh
 chown postgres:postgres /etc/pgpool-II/{failover.sh,follow_primary.sh}

2、修改脚本

vi /etc/pgpool-II/failover.sh
--------------------input------------------------------
PGHOME=/usr/local/pgsql
SSH_KEY_FILE=id_rsa

3、修改脚本

vi /etc/pgpool-II/follow_primary.sh

--------------------input------------------------------

PGHOME=/usr/local/pgsql
ARCHIVEDIR=/archive
SSH_KEY_FILE=id_rsa

9.2 在线恢复脚本

1、拷贝模版

 cp -p /etc/pgpool-II/sample_scripts/recovery_1st_stage.sample /usr/local/pgsql/data/recovery_1st_stage
cp -p /etc/pgpool-II/sample_scripts/pgpool_remote_start.sample /usr/local/pgsql/data/pgpool_remote_start
chown postgres:postgres /usr/local/pgsql/data/{recovery_1st_stage,pgpool_remote_start}

2、修改脚本

vi /usr/local/pgsql/data/recovery_1st_stage

--------------------input------------------------------
PGHOME=/usr/local/pgsql
ARCHIVEDIR=/archvie
REPLUSER=repl
SSH_KEY_FILE=id_rsa

3、修改脚本

vi /usr/local/pgsql/data/pgpool_remote_start

--------------------input------------------------------
PGHOME=/usr/local/pgsql
SSH_KEY_FILE=id_rsa

su - postgres
psql template1 -c "CREATE EXTENSION pgpool_recovery"
psql postgres -c "CREATE EXTENSION pgpool_recovery"
psql testdb -c "CREATE EXTENSION pgpool_recovery"

9.3

su - postgres
cd /usr/local/pgpool/etc 
vi escalation.sh

--------------------input------------------------------
SSH_KEY_FILE=id_rsa
PGPOOLS=(node1 node2 node3)
VIP=192.168.10.191
DEVICE=ens192

9.4 将配置脚本拷贝到每个节点

cd /usr/local/pgpool/etc/
scp follow_primary.sh failover.sh node2:/etc/
scp follow_primary.sh failover.sh node3:/usr/local/pgpool/etc/
scp $PGDATA/recovery_1st_stage node2:$PGDATA/
scp $PGDATA/recovery_1st_stage node3:$PGDATA/
scp $PGDATA/pgpool_remote_start node2:$PGDATA/
scp $PGDATA/pgpool_remote_start node3:$PGDATA/
scp escalation.sh node2:/usr/local/pgpool/etc/
scp escalation.sh node3:/usr/local/pgpool/etc/

到此为止：打快照 PGPOOLl-II INSTALL。